package busstopfinder.db;

import java.util.ArrayList;
import java.util.List;

import busstopfinder.ui.Demo;
import busstopfinder.ui.IconMarker;
import busstopfinder.ui.Marker;

import android.bustopfinder.R;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.graphics.Color;
import android.util.Log;

public class DatabaseWraper {
	
	public static final String DB_NAME = "localdatabase";
	public static final int DB_VERSION = 5;
	
	public static final String TABLE_NAME = "stops";
	
	public static final String KEY_ROW_ID = "_id";
	public static final String KEY_LATITUDE = "lat";
	public static final String KEY_LONGITUDE = "lon";
	public static final String KEY_ALTITUDE = "alt";
	public static final String KEY_STOP_NAME = "name";
	public static final String KEY_LINES_IN_STOP = "lines"; // space separated values
	
	private static final String TAG = "DatabaseAdapter";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;
    
    private final Context mCtx;
    
    public DatabaseWraper(Context c) {
    	this.mCtx = c;
    }
    
    
    public List<Marker> getMarkers(double minLat, double maxLat, double minLon, double maxLong) {
    	this.open();
    	
    	Log.w(TAG, "inside get Markers");
    	
    	// TODO: filter by lat/long (where clause)
    	Cursor mCursor = mDb.query(true, TABLE_NAME, new String[] 
    				{KEY_ROW_ID, KEY_STOP_NAME,KEY_LATITUDE, KEY_LONGITUDE,KEY_ALTITUDE, KEY_LINES_IN_STOP}, 
    				/*KEY_LATITUDE + " between " + minLat + " AND " + maxLat*/null, null,
                    null, null, null, null);
        
    	Log.w(TAG, "new cursor #rows: " + mCursor.getCount());
    	
    	ArrayList<Marker> mList = new ArrayList<Marker>();
    	Bitmap icon=BitmapFactory.decodeResource(Demo.mCtx.getResources(), R.drawable.botao_template);
    	
    	mCursor.moveToFirst();
    	while (!mCursor.isAfterLast()) {
    		String name = mCursor.getString(1);
    		double lat = mCursor.getDouble(2);
    		double lon = mCursor.getDouble(3);
    		double alt = mCursor.getDouble(4);
    		String lines = mCursor.getString(5);
    		
    		Marker m = new IconMarker(name, lat, lon, alt, Color.DKGRAY, icon);
    		m.setBusLines(lines);
    		mList.add(m);
    		
    		Log.w(TAG, "New marker. Name=" + name + ",1stcoord="+lat+",2ndcoord="+lon);
    		
    		mCursor.moveToNext();
    	}
    	
    	this.close();

    	return mList;
    	
    }
    
    
    private void open() throws SQLException {
    	try {
    		mDbHelper = new DatabaseHelper(mCtx);
    		mDb = mDbHelper.getWritableDatabase();
    	} 
    	catch (SQLException e) {
    		Log.e(TAG, "SQL exception: " + e.getMessage());
    	}
        Log.w(TAG, "open() sucessful");
    }
    
    private void close() {
        mDbHelper.close();
        Log.w(TAG, "close() sucessful");
    }
    
    private void createEntry(String stopName, double latitude, double longitude, double altitude, String linesInStop) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_STOP_NAME, stopName);
        initialValues.put(KEY_LATITUDE, new Double(latitude));
        initialValues.put(KEY_LONGITUDE, new Double(longitude));
        initialValues.put(KEY_ALTITUDE, new Double(altitude));
        initialValues.put(KEY_LINES_IN_STOP, linesInStop);
        
        mDb.insert(TABLE_NAME, null, initialValues);
	}
	
	private boolean deleteEntry(String stopName) {
	    return mDb.delete(TABLE_NAME, KEY_STOP_NAME + "=\"" + stopName+ "\"", null) > 0;
	}
	
	private void clear() {
		
		this.mDb.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
		this.mDb.execSQL(getDbCreate());
		
	}
	
	public void seed() {
		this.open();
		this.clear();
		
		createEntry("c2 1", 38.7560390, -9.1566320, 135, "12");
		createEntry("c2 2", 38.7559580, -9.1564965, 135, "12 703");
		createEntry("horto campo grande", 38.754164, -9.154366, 135, "703 790");
		createEntry("alin", 38.76647,  -9.151972, 135, "12 703 790");
		
		this.close();

	}
    
    
    
    
    private static String getDbCreate() {
    	
    	String create = "create table " 
    						+ TABLE_NAME + " (" 
    						+ KEY_ROW_ID + " integer primary key autoincrement, " 
    						+ KEY_LATITUDE + " double not null, "
    						+ KEY_LONGITUDE + " double not null, "
    						+ KEY_ALTITUDE + " double not null, "
    						+ KEY_STOP_NAME + " string, "
    						+ KEY_LINES_IN_STOP + " text not null);";
    	
    	return create;
    }
    
    private class DatabaseHelper extends SQLiteOpenHelper  {

    	DatabaseHelper(Context context) {
            super(context, DB_NAME, null, DB_VERSION);
        }

    	@Override
    	public void onCreate(SQLiteDatabase db) {
    		db.execSQL(getDbCreate());
    		Log.w(TAG, "database create executed");
    		
    	}

    	@Override
    	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    		Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
            onCreate(db);
            
    	}

    }
    
    
	
}
